﻿
CREATE VIEW [dbo].[vw_Quotes_per_Week]
AS
SELECT     TOP (100) PERCENT S.Description AS Security, S.TickerSymbol, E.Description AS Exchange, D.Year, D.WeekNr, dbo.fn_GetOpeningQuoteWeek(D.Year, D.WeekNr, 
                      S.TickerSymbol) AS [Open], dbo.fn_GetClosingQuoteWeek(D.Year, D.WeekNr, S.TickerSymbol) AS [Close], MIN(Q.LowValue) AS Min, MAX(Q.HighValue) AS Max, 
                      MIN(D.DateTime) AS FirstDay,
                      SUM(Q.Volume) AS Volume
FROM         dbo.Quote AS Q INNER JOIN
                      dbo.Security AS S ON Q.SecurityID = S.SecurityID INNER JOIN
                      dbo.Exchange AS E ON S.ExchangeID = E.EchangeID INNER JOIN
                      dbo.DateTime AS D ON Q.DateTimeID = D.DateTimeID
GROUP BY D.Year, D.WeekNr, S.Description, S.TickerSymbol, E.Description
ORDER BY D.Year, D.WeekNr